#!/usr/bin/env python
# encoding: utf-8
'''
@author: 迟乐飞 
@contact: lefei_chi@foxmail.com
@software: pycharm
@file: data_stream_by_calendar.py
@time: 2019-11-27 15:24
@desc:
'''


class DataStream(object):
    """
    每日数据的更新，跟热更新
    """

    def house_date_yesterday(self):
        """
        房屋数据昨日更新
        :return:
        """

        update_insert_sql = """
        insert into wh_report_calendar(income,airbnb_check_out_count,zhenguo_check_out_count,tujia_check_out_count,other_check_out_count,order_check_out_count,house_name, city_name,work_name,online_sale,house_id,lodge_id,city_id,work_id,online_sale_id,offline_sale,offline_sale_id,rank_type,daily_rent,dt,online_night,offline_night,order_night,available_night,gmv)
SELECT
	whc.income,
	sum( IF(whc.checkout_channel = 20,1,0 ) ) airbnb_check_out_count ,
	sum( IF(whc.checkout_channel = 25,1,0 ) ) zhenguo_check_out_count,
	sum( IF(whc.checkout_channel = 3,1,0 ) ) tujia_check_out_count,
	sum( IF(whc.checkout_channel not in (20,25,3),1,0 )) other_check_out_count,
	SUM(whc.checkout_orders) as order_check_out_count ,
	whs.title house_name,
	whs.city_name,
	whs.work_name,
	whs.online_sale,
	whc.house_id,
	whs.lodge_id,
	whs.city_id,
	whs.work_id,
	whs.online_sale_id,
	whs.offline_sale,
	whs.offline_sale_id,
	0 as rank_type,
	sum(whc.rent_day) daily_rent,
	whc.dt,
	sum(IF(whc.online_stock = 1 OR whc.have_order= 1,1,0 ) ) online_night,
	sum(IF(whc.online_stock = 1 AND whc.rent_stock = 0,1,0 ) ) offline_night,
	sum(IF(whc.have_order = 1 ,1,0 ) ) order_night,
	sum(IF(whc.online_stock = 1 AND whc.rent_stock = 1,1,0 ) ) available_night,
	round(sum( income + compensate_income +other_income) ) gmv
FROM
	youjia_report.wh_house_static whs
	LEFT JOIN youjia_report.wh_house_time_node whtn ON whtn.house_id = whs.house_id
	LEFT JOIN youjia_report.wh_house_calendar whc ON whc.house_id = whs.house_id 
WHERE
	whs.house_state >= 100 AND ( whc.online_stock = 1 OR have_order = 1 ) 
	AND whs.own_type IN ( 1, 2, 3 ) 
	AND whc.order_type <> 3 
	AND whc.dt = CURRENT_DATE -1
GROUP BY whc.house_id,whc.dt
	
        """
        pass

    def sale_date_yesterday(self):
        """
        销售数据昨日更新
        :return:
        """
        update_insert_sql= """
        insert into wh_region_calendar(income,airbnb_check_out_count,zhenguo_check_out_count,tujia_check_out_count,other_check_out_count,order_check_out_count,city_name,work_name,online_sale,city_id,work_id,online_sale_id,offline_sale,offline_sale_id,rank_type,daily_rent,dt,online_night,offline_night,order_night,available_night,gmv)

SELECT
	sum(whc.income) income,
	sum( IF(whc.checkout_channel = 20,1,0 ) ) airbnb_check_out_count ,
	sum( IF(whc.checkout_channel = 25,1,0 ) ) zhenguo_check_out_count,
	sum( IF(whc.checkout_channel = 3,1,0 ) ) tujia_check_out_count,
	sum( IF(whc.checkout_channel not in (20,25,3),1,0 )) other_check_out_count,
	SUM(whc.checkout_orders) as order_check_out_count ,
	whs.city_name,
	whs.work_name,
	whs.online_sale,
	whs.city_id,
	whs.work_id,
	whs.online_sale_id,
	whs.offline_sale,
	whs.offline_sale_id,
	1 as rank_type,
	sum(whc.rent_day) rent_day,
	whc.dt,
	sum( IF(whc.online_stock = 1 OR whc.have_order= 1,1,0 ) ) online_night,
	sum(IF(whc.online_stock = 1 AND whc.rent_stock = 0,1,0 ) ) offline_night,
	sum(IF(whc.have_order = 1 ,1,0 ) ) order_night,
	sum(IF(whc.online_stock = 1 AND whc.rent_stock = 1,1,0 ) ) available_night,
	round(sum( income + compensate_income +other_income) ) gmv
FROM
	youjia_report.wh_house_static whs
	LEFT JOIN youjia_report.wh_house_time_node whtn ON whtn.house_id = whs.house_id
	LEFT JOIN youjia_report.wh_house_calendar whc ON whc.house_id = whs.house_id 
WHERE
	whs.house_state >= 100 
	AND ( whc.online_stock = 1 OR have_order = 1 ) 
	AND whs.own_type IN ( 1, 2, 3 ) 
	AND whc.order_type <> 3 
	AND whc.dt = CURRENT_DATE -1
GROUP BY whs.online_sale_id,whc.dt on duplicate key update `online_stock` = values(online_stock),
                              modify_time=values(modify_time)
	"""
        pass

    def work_date_yesterday(self):
        """
        门店数据昨日更新
        :return:
        """
        update_insert_sql= """"""

        pass

    def city_date_yesterday(self):
        """
        城市数据昨日更新
        :return:
        """
        update_insert_sql= """"""

        pass

    def house_date_daily(self):
        """
        房屋数据日更新
        :return:
        """
        pass

    def sale_date_daily(self):
        """
        销售数据日更新
        :return:
        """
        pass

    def work_date_daily(self):
        """
        门店数据日更新
        :return:
        """
        pass

    def city_date_daily(self):
        """
        城市数据日更新
        :return:
        """
        pass
